package com.orange.app.office.util;

import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelUtil {

    /**
     * 读取excel内容
     * 
     * @param input
     * @return
     * @throws Exception
     */
    public static List<List<Object>> readExcel(InputStream input) throws Exception {
        List<List<Object>> datas = new ArrayList<List<Object>>();
        Workbook workBook = WorkbookFactory.create(input);
        Sheet sheet = workBook.getSheetAt(0);
        for (Row row : sheet) {
            List<Object> data = new ArrayList<Object>();
            boolean isEmptyRow = true;
            for (Iterator<Cell> iter = row.iterator(); iter.hasNext();) {
                Cell cell = iter.next();
                Object value = getCellValue(cell);
                data.add(value);
                isEmptyRow = false;
            }
            if (!isEmptyRow) {
                datas.add(data);
            }
        }
        return datas;
    }

    /**
     * 获取单元格的值
     * 
     * @param cell
     * @return
     * @throws Exception
     */
    @SuppressWarnings("deprecation")
    public static Object getCellValue(Cell cell) throws Exception {
        Object value;
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            // 字符串
            value = cell.getStringCellValue().trim();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            // 数字
            if (DateUtil.isCellDateFormatted(cell)) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
            } else {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                value = cell.getStringCellValue().trim();
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            // Boolean
            value = Boolean.valueOf(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            // 公式
            value = cell.getCellFormula();
            break;
        case Cell.CELL_TYPE_BLANK:
            // 空值
            value = "";
            break;
        case Cell.CELL_TYPE_ERROR:
            // 故障
            value = "非法字符";
            break;
        default:
            value = "";
            break;
        }
        return value;
    }

    /**
     * 写excel Sheet
     * 
     * @param sheet
     * @param datas
     * @throws Exception
     */
    public static void writeExcelSheet(Sheet sheet, List<List<String>> datas) throws Exception {
        Row row = null;
        for (int i = 0, len = datas.size(); i < len; i++) {
            List<String> data = datas.get(i);
            row = sheet.createRow(i);
            for (int j = 0, size = data.size(); j < size; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(data.get(j));
            }
        }
    }

    /**
     * 写excel Sheet
     * 
     * @param sheet
     * @param datas
     * @throws Exception
     */
    public static void writeExcelSheet(Sheet sheet, List<List<String>> datas, CellStyle headStyle,
            CellStyle contentStyle) throws Exception {
        Row row = null;
        for (int i = 0, len = datas.size(); i < len; i++) {
            List<String> data = datas.get(i);
            row = sheet.createRow(i);
            if (i == 0) {
                row.setHeightInPoints(30);
            } else {
                row.setHeightInPoints(25);
            }
            for (int j = 0, size = data.size(); j < size; j++) {
                Cell cell = row.createCell(j);
                if (i == 0) {
                    sheet.setColumnWidth(j, 5120);
                    cell.setCellStyle(headStyle);
                } else {
                    cell.setCellStyle(contentStyle);
                }
                cell.setCellValue(data.get(j));
            }
        }
    }

    public static CellStyle getHeadStyle(Workbook workbook) throws Exception {
        return getCellStyle(workbook, true);
    }

    public static CellStyle getContentStyle(Workbook workbook) throws Exception {
        return getCellStyle(workbook, false);
    }

    /**
     * 获取单元格样式
     * 
     * @param workbook
     * @param isHead
     * @return
     * @throws Exception
     */
    @SuppressWarnings("deprecation")
    private static CellStyle getCellStyle(Workbook workbook, boolean isHead) throws Exception {
        CellStyle style = workbook.createCellStyle();

        // 水平居左
        style.setAlignment(HorizontalAlignment.LEFT);
        // 垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        Font font = workbook.createFont();
        font.setFontName("Microsoft YaHei");
        font.setFontHeightInPoints((short) 10);// 设置字体大小
        if (isHead) {
            font.setBold(true);
        }
        style.setFont(font);

        if (isHead) {
            // 设置excel中的背景
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        }

        // 加边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);
        return style;
    }

}
